/**
 * 
 */
package cz.geoserver.KMLpublisher.util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import cz.geoserver.KMLpublisher.db.SQLExecutor;
import cz.geoserver.KMLpublisher.db.model.GeometryFeature;

/**
 * Class models a row of database result for incoming SQL query 
 * @author mkepka
 *
 */
public class GeomFeatureUtil{
    /**
     * Arrays of possible forms of parameters
     */
    private String[] parWhere = {"WHERE", "where"};
    private String[] parSelect = {"SELECT", "select"};

    /**
     * Constructor of class
     */
    public GeomFeatureUtil(){
    }

    /**
     * Method to sends query to executing to DBMS and creates list of objects from ResultSet
     * @param query String SQL SELECT command to send to DBMS 
     * @return List of objects from ResultSet or empty list if ResultSet returns no results
     * @throws SQLException Throws exception if there occurs any problems during executing SQL command in DBMS
     * @throws WrongSQLException Throws exception if SQL command does not contain SELECT 
     */
    public List<GeometryFeature> getGeomFeaturesByQuery(String query) throws SQLException {
        boolean isNotSelect = findParameter(query, parSelect).isEmpty();
        if(!isNotSelect){
            ResultSet res = SQLExecutor.getInstance().executeQuery(query);
            List<GeometryFeature> featList = generateGeomFeatureList(res);
            return featList;
        }
        else{
            throw new SQLException("SQL command does not contain SELECT that is reqiured!");
        }
    }

    /**
     * Method to sends query to executing to DBMS and creates list of objects from ResultSet.
     * There is added BBOX to select data only from given extend 
     * @param query String SQL SELECT command to send to DBMS
     * @param xmin Minimal X coordinate (west)
     * @param xmax Maximal X coordinate (east)
     * @param ymin Minimal Y coordinate (south)
     * @param ymax Minimal Y coordinate (north)
     * @return List of objects from ResultSet or empty list if ResultSet returns no results
     * @throws SQLException Throws exception if there occurs any problems during executing SQL command in DBMS
     */
    public List<GeometryFeature> getGeomFeaturesByQueryInBBOX(String query, double xmin, double xmax, double ymin, double ymax) throws SQLException{
        boolean isNotSelect = findParameter(query, parSelect).isEmpty();
        if(!isNotSelect){
            String polygon = "POLYGON(("+xmin+" "+ymax+", "+xmax+" "+ymax+", "+xmax+" "+ymin+", "+xmin+" "+ymin+", "+xmin+" "+ymax+"))";
            String spatQuery = " WHERE the_geom && transform(polygonFromText('"+polygon+"', 4326), st_srid(the_geom)) AND ";
            query = query.replace(findParameter(query, parWhere), spatQuery);
            query = query.replace(";", " LIMIT 50;");
            ResultSet res = SQLExecutor.getInstance().executeQuery(query);
            List<GeometryFeature> featList = generateGeomFeatureList(res);
            return featList;
        }
        else{
            throw new SQLException("SQL command does not contain SELECT that is reqiured!");
        }
    }

    /**
     * Method generates list of objects returned as result from DB. 
     * If result does not contain any result method returns empty list.
     * @param res ResultSet returned from DB to query
     * @return List of object returned from DB or empty list if result contain no results
     */
    protected List<GeometryFeature> generateGeomFeatureList(ResultSet res) {
        try {
            List<GeometryFeature> featureList = new LinkedList<GeometryFeature>();
            while (res.next()==true) {
                GeometryFeature geomFeat = new GeometryFeature(res);
                featureList.add(geomFeat);
            }
            return featureList;
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Methods for finding parameter in query from possible forms of parameter.
     * Parameters can be case insensitive
     * @param query Query where we want to find parameter
     * @param possibleString Array of possible form of parameter
     * @return Returns correct form of parameter or empty String if parameter is not in query
     */
    private String findParameter(String query, String[] possibleString){
        String paramName = "";
        for(int i = 0; i<possibleString.length; i++){
            boolean contain = query.contains(possibleString[i]);
            if(contain){
                paramName = possibleString[i];
            }
        }
        return paramName;
    }
}